package main

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"log"
	"strings"
)

//数据库配置
const (
	userName = "balin"
	password = "Balin19961030"
	ip       = "rm-bp12dq1216gel01098o.mysql.rds.aliyuncs.com"
	port     = "3306"
	dbName   = "snkrgram_db"
)

var DB *sql.DB

func InitDB() {
	path := strings.Join([]string{userName, ":", password, "@tcp(", ip, ":", port, ")/", dbName, "?charset=utf8"}, "")
	DB, _ = sql.Open("mysql", path)
	DB.SetMaxIdleConns(10)
	DB.SetMaxIdleConns(10)
	if err := DB.Ping(); err != nil {
		fmt.Println("open database fail")
		return
	}
	fmt.Println("open database suess")
}
func QueryAndParseRows(Db *sql.DB, queryStr string) []map[string]string {
	rows, err := Db.Query(queryStr)
	defer rows.Close()
	if err != nil {
		fmt.Printf("查询出错:\nSQL:\n%s, 错误详情:%s\n", queryStr, err.Error())
		return nil
	}
	//获取列名cols
	cols, _ := rows.Columns()
	if len(cols) > 0 {
		var ret []map[string]string
		for rows.Next() {
			buff := make([]interface{}, len(cols))
			data := make([][]byte, len(cols)) //数据库中的NULL值可以扫描到字节中
			for i, _ := range buff {
				buff[i] = &data[i]
			}
			rows.Scan(buff...) //扫描到buff接口中，实际是字符串类型data中
			//将每一行数据存放到数组中
			dataKv := make(map[string]string, len(cols))
			for k, col := range data { //k是index，col是对应的值
				//fmt.Printf("%30s:\t%s\n", cols[k], col)
				dataKv[cols[k]] = string(col)
			}
			ret = append(ret, dataKv)
		}
		return ret
	} else {
		return nil
	}
}

//任意可序列化数据转为Json,便于查看
func Data2Json(anyData interface{}) string {
	JsonByte, err := json.Marshal(anyData)
	if err != nil {
		log.Printf("数据序列化为json出错:\n%s\n", err.Error())
	}
	return string(JsonByte)
}

func main() {
	//获取数据库控制器DB
	DB, err := GetDb("data", 3306, "root", "root")
	if err != nil {
		log.Printf("获取数据库控制器出错:%s\n", err.Error())
	}
	defer DB.Close() //延迟关闭数据库控制器,释放数据库连接
	//单行数据查询
	showMasterStatus := QueryAndParse(Db, "show master status")
	log.Printf("单行数据-数据库状态:%v\n", Data2Json(showMasterStatus))
	log.Printf("单行数据-数据库状态-File:%v\n", showMasterStatus["File"])

	//多行数据查询
	showProcessList := QueryAndParseRows(Db, "show processlist")
	log.Printf("多行数据-进程信息:%v\n", Data2Json(showProcessList))
	log.Printf("多行数据-进程信息-Host:%v\n", showProcessList[0]["Host"])
}
